import pandas as pd
import re
from openpyxl import load_workbook
import os
from datetime import datetime
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.styles import Border, Side

# 1. 数据准备
# 1. 重新读取文件（跳过首行干扰）
restock_df = pd.read_excel(os.getcwd(),
    'Preprocessed_Data.xlsx',
    header=0,  # 指定第二行为列名行
    usecols=lambda col: "Unnamed" not in col  # 自动过滤空列
)

# 2. 验证列名（关键步骤）
print("修正后列名:", list(restock_df.columns))
template_path = 'gystempRestockOrder.xlsx'


# 3. 件数解析函数（处理多种格式）
def parse_quantity(qty_str):
    if pd.isna(qty_str):
        return 0
    qty_str = str(qty_str)
    
    # 处理特殊格式
    if '各' in qty_str:
        # 提取"各"后面的数字
        match = re.search(r'各(\d+)', qty_str)
        if match:
            return int(match.group(1))
        return 0
    
    # 直接提取数字
    match = re.search(r'\d+', qty_str)
    return int(match.group(0)) if match else 0


def parse_colors(color_str):
    """解析颜色字符串为列表"""
    if pd.isna(color_str) or not color_str:
        return [""]
    
    # 支持多种分隔符：/、\、,、空格、顿号
    separators = ['/', '\\', ',', '，',' ', '、', ';', '|','.']
    color_str = str(color_str).strip()
    
    for sep in separators:
        if sep in color_str:
            return [c.strip() for c in color_str.split(sep) if c.strip()]
    
    return [color_str]


# 2. 码号解析函数（使用正则表达式）
def parse_sizes(size_str):
    if pd.isna(size_str):
        return []
    # 统一处理特殊字符和中文
    size_str = str(size_str).lower().replace(' ', '').replace('，', ',')
    size_str = size_str.replace('均', '均码').replace('均码', '均码,')
    
    # 正则匹配码号 (l, xl, xxl, 均码等)
    pattern = r'(均码|[sml]|x{0,2}l|\d{2,3})'
    sizes = re.findall(pattern, size_str)
    return [s.upper() for s in sizes if s]


def convert_huonum(value):
    try:
        # 处理浮点数或可转为浮点数的字符串
        if isinstance(value, float) or (isinstance(value, str) and '.' in value):
            num = float(value)
            return str(int(num)) if num == int(num) else str(num)
        # 处理整数或其他类型
        return str(int(value)) if str(value).isdigit() else str(value)
    except (ValueError, TypeError):
        return str(value)  # 保底处理，保留原始字符串

# 4. 处理每行数据（拆解多个码号）
def run_main():
    processed_data = []

    # 遍历每行数据
    for _, row in restock_df.iterrows():
        sizes = parse_sizes(row['码号'])

        # column_name = '码号'  # 定义变量方便维护
        # sizes = parse_sizes(row.get(column_name, ''))  # 使用get()避免KeyError[3](@ref)
        qty = parse_quantity(row['件数'])

        if not sizes:  # 无有效码号时作为一行处理
            processed_data.append({
                '供应商': row['供应商'],
                '货号': row['货号'],
                '颜色': row['颜色'],
                '码号': '均码',
                '件数': qty,
                '地区': row['地区']
            })
        else:  # 拆解多个码号
            for size in sizes:
                processed_data.append({
                    '供应商': row['供应商'],
                    '货号': row['货号'],
                    '颜色': row['颜色'],
                    '码号': size,
                    '件数': qty,
                    '地区': row['地区']
                })

    processed_df = pd.DataFrame(processed_data)


    # 按货号、颜色、码号分组，合并地区件数
    merged_df = processed_df.groupby(['货号', '颜色', '码号']).apply(
    lambda x: pd.Series({
        '供应商': x['供应商'].iloc[0],  # 取第一个供应商（假设同一货号供应商相同）
        '件数总和': x['件数'].sum(),
        '地区数据': x.groupby('地区')['件数'].sum().to_dict()  # 存储地区与件数的映射
    })
).reset_index()

    print(merged_df.head())  # 检查合并后的数据

    # 定义地区列映射关系（模板中G6-V6对应列）
    region_columns = {
        "襄阳": "G", "安陆": "H", "云梦": "I", "孝感": "J",
        "孝昌": "K", "应城": "L", "新洲": "M", "广水": "N",
        "京山": "O", "大悟": "P", "红安": "Q", "宜城": "R",
        "枣阳": "S", "随州": "T", "荆门": "U", "襄州": "V"
    }
    current_time = datetime.now().strftime("%Y-%m-%d_%H-%M")
    # 构造以当前分钟命名的文件夹路径
    folder_path = os.path.join(os.getcwd(), current_time)

    # 创建文件夹（如果不存在）
    os.makedirs(folder_path, exist_ok=True)
    # 定义通用样式
    cell_font = Font(name='宋体', size=11)
    cell_alignment = Alignment(horizontal='center', vertical='center')
    border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    # 5. 按供应商分组输出
    for supplier, group in merged_df.groupby('供应商'):
        wb = load_workbook(template_path)
        ws = wb.active
        ws['C2'] = supplier
        ws['C3'] = group['件数总和'].sum()

        row_index = 7  # 从第7行开始填充
        serial_num = 1  # 序号起始
        for _, row in group.iterrows():
            ws[f'A{row_index}'] = serial_num
            huonum = convert_huonum(row['货号'])
            ws[f'D{row_index}'] = huonum
            ws[f'E{row_index}'] = row['颜色']
            ws[f'F{row_index}'] = row['码号'] if row['码号'] else "均码"

            # 填充 G ~ V 地区数据
            for region, qty in row['地区数据'].items():
                if region in region_columns:
                    col_letter = region_columns[region]
                    ws[f'{col_letter}{row_index}'] = qty

            # 设置 W列 =SUM(Gx:Vx)，X列 =B行 * Wx
            ws[f'W{row_index}'] = f"=SUM(G{row_index}:V{row_index})"
            ws[f'X{row_index}'] = f"=B{row_index}*W{row_index}"

            # 给 D ~ X 添加样式（宋体、11号、居中、加边框）
            for col in range(1, 25):  # D = 4, X = 24
                cell = ws.cell(row=row_index, column=col)
                cell.font = cell_font

                if col == 24 or col == 23:
                    cell.font = Font(name='宋体', color='FF0000', size=22)
                cell.alignment = cell_alignment
                cell.border = border

            row_index += 1
            serial_num +=1

        # 保存文件
        target_row = row_index
        # 合并 A-F 单元格并写入“地区”
        common_font = Font(name='宋体', color='FF0000', size=22)
        common_alignment = Alignment(horizontal='center', vertical='center')
        # A-F 合并，写入“地区”，并设置样式
        ws.merge_cells(start_row=target_row, start_column=1, end_row=target_row, end_column=6)
        merged_cell = ws.cell(row=target_row, column=1, value='地区合计')
        merged_cell.font = common_font
        merged_cell.alignment = common_alignment
        # G-X（列7到24）写入对应求和公式，设置样式
        for col in range(7, 25):  # G = 7, X = 24
            col_letter = get_column_letter(col)
            sum_range = f"{col_letter}{7}:{col_letter}{target_row - 1}"
            cell = ws.cell(row=target_row, column=col, value=f"=SUM({sum_range})")
            cell.font = common_font

            # if col == 24 or col == 23:
            #     cell.font = Font(name='宋体', color='FF0000', size=11)
    
            cell.alignment = common_alignment




        output_file = os.path.join(folder_path, f'{supplier}_RestockOrder.xlsx')
        wb.save(output_file)
        print(f'已生成: {output_file}')

    print('所有文件处理完成！')
# run_main()
if __name__ == "__main__":
   run_main()